
create or replace function count_pattern(string text, pattern text) returns int AS $$

# ---------------[ count_pattern(string text, pattern text) ]------------------
#
# Count the occurrences of pattern in string using Python method 
# string.count(pattern). 
# Returns 0 if no match is found
# RegEx not allowed, use count_pattern_re(text, text) instead (slower)
#
# Usage:
# select count_pattern('aaaXabbbX', 'X');  <- returns 2
#
# select count_pattern(numeric_column::text, '1') from mytable; <- convert to 
#                                                                  numeric
# -----------------------------------------------------------------------------

  try:
      pmatch= string.count(pattern)
  except:
      return(0)

  return(pmatch)

  $$ language 'plpythonu';


-- Fancier version of the above: RegEx allowed in pattern. RegEx processed with
-- module re

create or replace function count_pattern_re(string text, regex text) returns int AS $$

# ---------------[ count_pattern(string text, regex text) ]--------------------
#
# Count the occurrences of regex in string using Python module re for RegEx.
#
# Usage:
# select count_pattern_re('aaaXabbbx', '[Xx]');  <- returns 2
#
# Note: If regex are not necessary, use count_pattern() since it is faster.
#
# -----------------------------------------------------------------------------

  import re
  
  p= re.compile(regex)
    
  try:
      pmatch= len(p.findall(string)) ##string.count(pattern)
  except:
      return(None)

  return(pmatch)

  $$ language 'plpythonu';